package com.lightsaber.trade.cloth.dao.programme;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbcp.DelegatingCallableStatement;
import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.hibernate.criterion.Order;
import org.hibernate.jdbc.Work;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;
import org.springside.modules.orm.Page;

import com.lightsaber.trade.core.dao.BaseDao;
import com.lightsaber.trade.cloth.entity.category.Category;
import com.lightsaber.trade.cloth.entity.category.CategoryLeaf;
import com.lightsaber.trade.cloth.entity.category.CategoryProgrammeAttribute;
import com.lightsaber.trade.cloth.entity.programme.Programme;
import com.lightsaber.trade.cloth.entity.programme.ProgrammeStatus;
import com.lightsaber.trade.cloth.entity.programme.SubjectGroup;
import com.lightsaber.trade.cloth.entity.programme.SubjectGroupItem;
import com.lightsaber.trade.cloth.service.param.ParamService;

@Component
public class ProgrammeDao extends BaseDao<Programme, String> {

    private final static Logger log = LoggerFactory.getLogger(ProgrammeDao.class);

    public Page<Programme> findPage(final Programme programme, final Page<Programme> page) {

        eq("launchYear", programme.getLaunchYear());
        like("launchVersion", programme.getLaunchVersion());
        like("code", programme.getCode());
        eq("board", programme.getBoard());
        eq("discipline", programme.getDiscipline());
        eq("fundingMode", programme.getFundingMode());
        eq("studyMode", programme.getStudyMode());
        eq("programmeType", programme.getProgrammeType());
        eq("studylevel", programme.getStudylevel());
        eq("preEmploymentInService", programme.getPreEmploymentInService());
        eq("status", programme.getStatus());
        like("titleLong", programme.getTitleLong());
        addOrder(Order.asc("launchYear"));
        addOrder(Order.asc("launchVersion"));
        addOrder(Order.asc("code"));

        return findPage(page);
    }

    public long countByCodeAndYearAndVersion(final Programme programme) {
        eq("launchVersion", programme.getLaunchVersion());
        eq("launchYear", programme.getLaunchYear());
        eq("code", programme.getCode());

        return count();
    }

    public Page<Programme> viewResultSet(final Category category, final CategoryLeaf leaf,
            final Page<Programme> page) {
        Map<String, Object> values = new HashMap<String, Object>();

        String hql = generateHql(values, category, leaf, null, null);

        return findPage(page, hql, values);
    }

    @SuppressWarnings("unchecked")
    public List<Programme> filterByAttributes(final Category category, final CategoryLeaf leaf) {
        Map<String, Object> values = new HashMap<String, Object>();

        String hql = generateHql(values, category, leaf, null, null);

        return this.createQuery(hql, values).list();
    }

    public boolean checkLeafAssociatedProgrammeByProgrammeCode(final Category category,
            final CategoryLeaf leaf, final String programmeCode) {
        Map<String, Object> values = new HashMap<String, Object>();

        String hql = generateHql(values, category, leaf, programmeCode, null);

        List<Programme> list = this.createQuery(hql, values).list();
        if (list.isEmpty()) {
            return false;
        } else {
            return true;
        }
    }

    public List<Programme> getLeafAssociatedProgrammeByInService(final Category category,
            final CategoryLeaf leaf, final String inService) {
        Map<String, Object> values = new HashMap<String, Object>();

        String hql = generateHql(values, category, leaf, null, inService);
        // hql = hql.replace("select p from", "select distinct(p.code) from");

        return this.createQuery(hql, values).list();
    }

    private String generateHql(final Map<String, Object> values, final Category category,
            final CategoryLeaf leaf, final String programmeCode, final String inService) {
        List<CategoryProgrammeAttribute> attList = leaf.getCategoryProgrammeAttributes();
        Integer planningExercise = category.getPlanYear();
        // String bureau = leaf.getBureau();
        String fundingMode = leaf.getFundingMode();

        Map<String, String> map = new HashMap<String, String>();
        for (CategoryProgrammeAttribute att : attList) {
            String code = att.getProgrammeAttribute();
            String mapValue = "";
            if (map.containsKey(code)) {
                mapValue = map.get(code);
            }
            mapValue += att.getValue() + ",";
            map.put(code, mapValue);
        }

        // 1. Add launch year and launch version
        String hql = "select p from Programme p where ((p.launchYear =:launchYear and p.launchVersion in (:launchVersions1))"
                + " or (p.launchYear <:launchYear and  p.launchVersion in (:launchVersions2)))";
        values.put("launchYear", planningExercise + 1);

        String[] launchVersions1 = new String[] { "M01", "M02", "M03", "M04", "M05", "M06", "M07",
                "Spr", "Sum" };
        values.put("launchVersions1", launchVersions1);

        String[] launchVersions2 = new String[] { "M01", "M02", "M03", "M04", "M05", "M06", "M07",
                "M08", "M09", "M10", "M11", "M12", "Spr", "Sum", "Aut", "Win" };
        values.put("launchVersions2", launchVersions2);

        if (StringUtils.isNotBlank(fundingMode)) {
            hql += " and p.fundingMode =:fundingMode ";
            values.put("fundingMode", fundingMode);
        }

        // 2. Add property in Leaf TODO
        // if (StringUtils.isNotBlank(bureau)) {
        // hql += " and p.bureau =:bureau ";
        // values.put("bureau", bureau);
        // }

        // 3. Add attributes
        if (map.containsKey(ParamService.PROGRAMME_CODE)) {
            hql += " and p.code in (:codes)";
            values.put("codes", map.get(ParamService.PROGRAMME_CODE).split(","));
        }
        // if (map.containsKey(ParamService.IS_STREAM_OR_PROGRAMME)) {
        // hql += " and p.streamOrProgramme in (:streamOrProgramme)";
        // values.put("streamOrProgramme",
        // map.get(ParamService.IS_STREAM_OR_PROGRAMME).split(","));
        // }
        if (map.containsKey(ParamService.PROGRAMME_BOARD)) {
            hql += " and p.board in (:board)";
            values.put("board", map.get(ParamService.PROGRAMME_BOARD).split(","));
        }

        if (map.containsKey(ParamService.DISCIPLINE)) {
            hql += " and p.discipline in (:discipline)";
            values.put("discipline", map.get(ParamService.DISCIPLINE).split(","));
        }
        if (map.containsKey(ParamService.STUDY_MODE)) {
            hql += " and p.studyMode in (:studyMode)";
            values.put("studyMode", map.get(ParamService.STUDY_MODE).split(","));
        }
        if (map.containsKey(ParamService.LEVEL_OF_STUDY)) {
            hql += " and p.studylevel in (:studylevel)";
            values.put("studylevel", map.get(ParamService.LEVEL_OF_STUDY).split(","));
        }
        if (map.containsKey(ParamService.PROGRAMME_TYPE)) {
            hql += " and p.programmeType in (:programmeType)";
            values.put("programmeType", map.get(ParamService.PROGRAMME_TYPE).split(","));
        }
        if (map.containsKey(ParamService.SUBJECT_GROUP)) {
            hql += " and p.subjectGroup in (:subjectGroup)";
            values.put("subjectGroup", map.get(ParamService.SUBJECT_GROUP).split(","));
        }
        if (map.containsKey(ParamService.PRE_EMP_IN_SER)) {
            hql += " and p.preEmploymentInService in (:preEmploymentInServices)";
            values.put("preEmploymentInServices", map.get(ParamService.PRE_EMP_IN_SER).split(","));
        }
        if (map.containsKey(ParamService.DURATION)) {
            hql += " and p.duration in (:duration)";

            String[] durations = map.get(ParamService.DURATION).split(",");
            Integer[] du = new Integer[durations.length];
            for (int i = 0; i < durations.length; i++) {
                du[i] = Integer.valueOf(durations[i]);
            }

            values.put("duration", du);
        }

        if (map.containsKey(ParamService.QF_LEVEL)) {
            hql += " and p.qfLevel in (:qfLevel)";
            values.put("qfLevel", map.get(ParamService.QF_LEVEL).split(","));
        }

        if (map.containsKey(ParamService.PROGRAMME_STATUS)) {
            hql += " and p.status in (:status)";
            values.put("status",
                    ProgrammeStatus.convert(map.get(ParamService.PROGRAMME_STATUS).split(",")));
        }

        if (map.containsKey(ParamService.CLASSIFICATION)) {
            hql += " and p.classification in (:classification)";
            values.put("classification", map.get(ParamService.CLASSIFICATION).split(","));
        }

        if (map.containsKey(ParamService.AWARD)) {
            hql += " and p.award in (:award)";
            values.put("award", map.get(ParamService.AWARD).split(","));
        }
        if (map.containsKey(ParamService.GAR_TYPE)) {
            hql += " and p.garType in (:garType)";
            values.put("garType", map.get(ParamService.GAR_TYPE).split(","));
        }
        if (map.containsKey(ParamService.PAA)) {
            hql += " and p.paa in (:paa)";
            values.put("paa", map.get(ParamService.PAA).split(","));
        }
        if (map.containsKey(ParamService.SUB_PAA)) {
            hql += " and p.subPaa in (:subPaa)";
            values.put("subPaa", map.get(ParamService.SUB_PAA).split(","));
        }
        if (map.containsKey(ParamService.MAIN_STREAM)) {
            hql += " and p.mainStream in (:mainStream)";
            values.put("mainStream", map.get(ParamService.MAIN_STREAM).split(","));
        }

        // 4. Add point code
        if (StringUtils.isNotBlank(programmeCode)) {
            hql += " and p.code =:code";
            values.put("code", programmeCode);
        }

        if (StringUtils.isNotBlank(inService)) {
            hql += " and p.preEmploymentInService =:preEmploymentInService";
            values.put("preEmploymentInService", inService);
        }
        log.info("search sql : {}", hql);
        return hql;
    }

    public List<String> getAllDiscipline(final int exerciseYear, final String studyMode,
            final String campus, final String department) {
        Map<String, Object> map = new HashMap<String, Object>();

        String hql = "select distinct p.discipline from Programme p where p.launchYear <=:launchYear ";

        map.put("launchYear", exerciseYear);

        if (StringUtils.isNotBlank(studyMode) && StringUtils.isBlank(campus)) {
            hql += "  and p.studyMode=:studyMode";
            map.put("studyMode", studyMode);
        } else if (StringUtils.isNotBlank(studyMode) && StringUtils.isNotBlank(campus)) {
            hql += "  and p.studyMode=:studyMode";
            hql += " and p.id in(select oc.programme.id from OfferingCampus oc where oc.operatingCampus=:operatingCampus";
            if (StringUtils.isNotBlank(department)) {
                hql += " and oc.operatingUnit=:operatingUnit";
                map.put("operatingUnit", department);
            }
            hql += ")";
            map.put("studyMode", studyMode);
            map.put("operatingCampus", campus);
        } else if (StringUtils.isBlank(studyMode) && StringUtils.isNotBlank(campus)) {
            hql += " and p.id in(select oc.programme.id from OfferingCampus oc where oc.operatingCampus=:operatingCampus";
            if (StringUtils.isNotBlank(department)) {
                hql += " and oc.operatingUnit=:operatingUnit";
                map.put("operatingUnit", department);
            }
            hql += ")";
            map.put("operatingCampus", campus);
        }
        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public List<String> getDistinctDisciplineByCode(final int exerciseYear, final String code) {
        String hql = "select distinct p.discipline from Programme p where p.code=:code  and p.launchYear <=:launchYear ";
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("code", code);
        map.put("launchYear", exerciseYear);

        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public List<Integer> getMaxDurationByDiscipline(final String discipline) {
        String hql = "select Max(duration) from Programme where discipline=:discipline ";
        Map<String, String> map = new HashMap<String, String>();
        map.put("discipline", discipline);
        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public List<String> findByDiscipline(final String disciplineType,
            final String searchByInService, final Integer exerciseYear) {

        Map<String, Object> map = new HashMap<String, Object>();
        String hql = "select distinct(p.code) from Programme p where p.launchYear <=:launchYear "
                + " and p.discipline=:discipline ";
        map.put("launchYear", exerciseYear);
        map.put("discipline", disciplineType);
        if (StringUtils.isNotBlank(searchByInService)) {
            hql += " and p.preEmploymentInService=:preEmploymentInService ";
            map.put("preEmploymentInService", searchByInService);
        }
        Query q = this.createQuery(hql, map);
        return q.list();

    }

    public List<Programme> findByDisciplineAndCampusAndDep(final String discipline,
            final String operatingCampus, final String operatingUnit, final String studyMode) {

        Map<String, String> map = new HashMap<String, String>();
        String hql = "select p from Programme p where p.discipline=:discipline "
                + " and p.id in (select oc.programme.id from OfferingCampus oc where oc.operatingCampus=:operatingCampus and oc.operatingUnit=:operatingUnit)";

        if (StringUtils.isNotBlank(studyMode)) {
            hql += " and p.studyMode=:studyMode";
            map.put("studyMode", studyMode);
        }

        map.put("discipline", discipline);
        map.put("operatingCampus", operatingCampus);
        map.put("operatingUnit", operatingUnit);
        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public List<Programme> getFeederList() {
        eq("subjectGroup", true);
        isNull("subjectGroupFeeder");

        return list();
    }

    public List<Object> getAllProgrammeCodes() {
        String hql = "select distinct p.code from Programme as p";

        return this.createQuery(hql).list();
    }

    public List<Object> getAllFirstLaunchYears() {
        String hql = "select distinct p.firstLaunchYear from Programme as p";
        return this.createQuery(hql).list();
    }

    public List<String> distinctCodeByLaunchYear(final Integer launchYear) {

        Map<String, Object> map = new HashMap<String, Object>();
        String hql = "select distinct(p.code) from Programme p where p.launchYear <=:launchYear";
        map.put("launchYear", launchYear);
        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public List<Programme> findByCodeAndCurrentYear(final String code, final Integer launchYear) {
        eq("launchYear", launchYear);
        eq("code", code);
        return list();
    }

    public List<Programme> findByCodeAndLaunchYear(final String code, final Integer launchYear) {
        Map<String, Object> map = new HashMap<String, Object>();
        String hql = "select p from Programme p where p.launchYear < :launchYear and p.code=:code order by p.launchYear desc";
        map.put("launchYear", launchYear);
        map.put("code", code);
        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public List<Programme> findByCodeAndLaunchYearAndLaunchVersion(final String code,
            final Integer launchYear, final String version) {
        Map<String, Object> map = new HashMap<String, Object>();
        String hql = "select p from Programme p where p.launchYear = :launchYear and p.code=:code and p.launchVersion=:version";
        map.put("launchYear", launchYear);
        map.put("code", code);
        map.put("version", version);
        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public List<Programme> findFeeders(final SubjectGroup subjectGroup) {
        eq("code", subjectGroup.getProgrammeCode().getCode());
        eq("launchYear", subjectGroup.getPlanningExercise());
        ne("requireStream", true);
        isEmpty("streams");

        return list();
    }

    public List<Programme> findReceivers(final SubjectGroup subjectGroup) {
        String hql = "select p from Programme p where exists "
                + "(select 1 from SubjectGroup gp, SubjectGroupItem gpi "
                + "where gpi.subjectGroup=gp and gp=:subjectGroup and gpi.programmeCode.code=p.code) "
                + "and p.requireStream <> true and p.streams is empty";

        Query q = createQuery(hql);
        q.setEntity("subjectGroup", subjectGroup);

        return q.list();
    }

    public List<Programme> findByCodesAndLaunchYear(final String[] codes, final Integer launchYear) {
        Map<String, Object> map = new HashMap<String, Object>();
        String hql = "select p from Programme p where p.launchYear < :launchYear and p.code in(:codes)";
        map.put("launchYear", launchYear);
        map.put("codes", codes);
        Query q = this.createQuery(hql, map);
        return q.list();
    }

    public Map<String, String> checkCode(final String code, final String launchYear,
            final String launchVersion) {

        CodeWork codeWork = new CodeWork(code, launchYear, launchVersion);
        getSession().doWork(codeWork);

        return codeWork.getResultMap();

    }

    public static class CodeWork implements Work {

        private String code;

        private Map<String, String> resultMap = new HashMap<String, String>();

        private String launchYear;

        private String launchVersion;

        public CodeWork(final String code, final String launchYear, final String launchVersion) {
            super();
            this.code = code;
            this.launchYear = launchYear;
            this.launchVersion = launchVersion;
        }

        @Override
        public void execute(final Connection connection) throws SQLException {
            String sql = "{Call PKG_CHECK_PROG.P_CHECK_PROG(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
            DelegatingCallableStatement call = (DelegatingCallableStatement) connection
                    .prepareCall(sql);

            call.setString(1, code);
            call.setString(2, launchYear);
            call.setString(3, launchVersion);
            call.setString(4, "P");

            call.registerOutParameter(5, java.sql.Types.ARRAY, "RESULT_TYPE_ARRAY");
            call.registerOutParameter(6, java.sql.Types.ARRAY, "RESULT_MSG_ARRAY");

            call.registerOutParameter(7, java.sql.Types.VARCHAR);
            call.registerOutParameter(8, java.sql.Types.VARCHAR);
            call.registerOutParameter(9, java.sql.Types.VARCHAR);
            call.registerOutParameter(10, java.sql.Types.VARCHAR);
            call.registerOutParameter(11, java.sql.Types.VARCHAR);
            call.registerOutParameter(12, java.sql.Types.VARCHAR);
            call.registerOutParameter(13, java.sql.Types.VARCHAR);
            call.registerOutParameter(14, java.sql.Types.VARCHAR);

            call.execute();

            // String[] resultTypes = (String[]) call.getArray(5).getArray();
            // String[] resultMsgs = (String[]) call.getArray(6).getArray();
            // StringBuilder result = new StringBuilder();
            // for (int i = 0; i < resultTypes.length; i++) {
            // result.append(resultTypes[i] + ":" + resultMsgs[i] + "; ");
            // }
            // resultMap.put("result", result.toString());

            if (StringUtils.isBlank(call.getString(7))) {
                resultMap.put("message", "Can't get the 'discipline'!");
                return;
            }

            if (StringUtils.isBlank(call.getString(9))) {
                resultMap.put("message", "Can't get the 'fundingMode'!");
                return;
            }
            if (StringUtils.isBlank(call.getString(11))) {
                resultMap.put("message", "Can't get the 'studyMode'!");
                return;
            }
            if (StringUtils.isBlank(call.getString(13))) {
                resultMap.put("message", "Can't get the 'qfLevel'!");
                return;
            }

            resultMap.put("message", "YES");
            resultMap.put("discipline", call.getString(7));
            resultMap.put("discipline_label", call.getString(8));
            resultMap.put("studyMode", call.getString(9));
            resultMap.put("studyMode_label", call.getString(10));
            resultMap.put("fundingMode", call.getString(11));
            resultMap.put("fundingMode_label", call.getString(12));
            resultMap.put("qfLevel", call.getString(13));
            resultMap.put("qfLevel_label", call.getString(14));

        }

        public Map<String, String> getResultMap() {
            return resultMap;
        }

    }

    public List<Programme> findByCode(String code) {
        eq("code", code);
        return list();
    }

    public List<Programme> findReceivers(SubjectGroup subjectGroup,
            SubjectGroupItem subjectGroupItem) {
        String hql = "select p from Programme p where exists "
                + "(select 1 from SubjectGroup gp, SubjectGroupItem gpi "
                + "where gpi.subjectGroup=gp and gp=:subjectGroup and gpi=:subjectGroupItem and gpi.programmeCode.code=p.code) "
                + "and p.requireStream <> true and p.streams is empty";

        Query q = createQuery(hql);
        q.setEntity("subjectGroup", subjectGroup);
        q.setEntity("subjectGroupItem", subjectGroupItem);

        return q.list();
    }
}
